package com.kla.power.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kla.power.entity.DeviceEntity;
import com.kla.power.entity.StationEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 设备：逆变器、电表、组件、机器人、无人机 Mapper 接口
 * </p>
 * @author WuZH
 * @since 2023-06-23
 */
@Mapper
public interface DeviceMapper extends BaseMapper<DeviceEntity> {


    /**
     * 获取逆变器数量
     * @param deviceEntity
     * @return
     */
    //@Select (value = "<script>SELECT di.id,di.deviceTitle,di.deviceSn,di.stationId,di.address,si.stationName,isi.gridPower,di.power,di.parentId,di.deviceType, col.deviceSn AS parentName,isi.todayEnergy AS todayEnergy,isi.createTime \n" +
    //        "FROM device_info di LEFT JOIN station_info si ON di.stationId = si.id LEFT JOIN device_info col ON di.parentId = col.id\n" +
    //        "LEFT JOIN (SELECT cisi.*\n" +
    //        "FROM inverter_electric_info cisi\n" +
    //        "   JOIN (\n" +
    //        "   SELECT serialNumber, MAX(createTime) AS createTime\n" +
    //        "   FROM inverter_electric_info \n" +
    //        "   WHERE DATE_FORMAT(createTime, '%Y-%m-%d') = DATE_FORMAT(current_date(), '%Y-%m-%d')\n" +
    //        "   GROUP BY serialNumber\n" +
    //        "   ) isi_temp ON cisi.serialNumber = isi_temp.serialNumber AND cisi.createTime = isi_temp.createTime) isi\n" +
    //        "   ON di.deviceSn = isi.serialNumber\n" +
    //        "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
    //        "<if test='deviceEntity.stationId!=null'> AND si.id=#{deviceEntity.stationId}</if>\n" +
    //        "<if test='deviceEntity.deviceType!=null'> AND di.deviceType=#{deviceEntity.deviceType}</if>\n" +
    //        "<if test='deviceEntity.merchantId!=null'> AND " +
    //        "si.merchantId IN (SELECT id\n" +
    //        "                        FROM (SELECT t1.id,\n" +
    //        "                                     t1.parentId,\n" +
    //        "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
    //        "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
    //        "                                   (SELECT @pids := #{deviceEntity.merchantId}) t2) t3\n" +
    //        "                        where ischild != 0\n" +
    //        "                        UNION\n" +
    //        "                        SELECT id\n" +
    //        "                        FROM merchant_info pi\n" +
    //        "                        WHERE pi.id =#{deviceEntity.merchantId})</if>\n" +
    //        "LIMIT #{deviceEntity.page}, #{deviceEntity.limit}\n" +
    //        "</trim></script>")
    @Select ( value = "<script>SELECT di.id,\n" +
            "       di.deviceTitle,\n" +
            "       di.deviceSn,\n" +
            "       di.stationId,\n" +
            "       di.address,\n" +
            "       si.stationName,\n" +
            "       liei.gridPower,\n" +
            "       di.power,\n" +
            "       di.parentId,\n" +
            "       di.deviceType,\n" +
            "       col.deviceSn     AS parentName,\n" +
            "       liei.todayEnergy AS todayEnergy,\n" +
            "       liei.updateTime AS createTime\n" +
            "FROM device_info di\n" +
            "         LEFT JOIN station_info si ON di.stationId = si.id\n" +
            "         LEFT JOIN device_info col ON di.parentId = col.id\n" +
            "         LEFT JOIN latest_inverter_electric_info liei ON liei.serialNumber = di.deviceSn\n" +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='deviceEntity.stationId!=null'> AND si.id=#{deviceEntity.stationId}</if>\n" +
            "<if test='deviceEntity.deviceType!=null'> AND di.deviceType=#{deviceEntity.deviceType}</if>\n" +
            "<if test='deviceEntity.merchantId!=null'> AND " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +
            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids := #{deviceEntity.merchantId}) t2) t3\n" +
            "                        where ischild != 0\n" +
            "                        UNION\n" +
            "                        SELECT id\n" +
            "                        FROM merchant_info pi\n" +
            "                        WHERE pi.id = #{deviceEntity.merchantId})</if>" +
            "LIMIT #{deviceEntity.page}, #{deviceEntity.limit}</trim></script>")
    List<DeviceEntity> queryList (@Param ("deviceEntity") DeviceEntity deviceEntity);


    /**
     * 获取逆变器数量
     * @param deviceEntity
     * @return
     */
    @Select (value = "<script>SELECT COUNT(1)\n" +
            "FROM device_info di LEFT JOIN station_info si ON di.stationId = si.id LEFT JOIN device_info col ON di.parentId = col.id\n" +
            "LEFT JOIN latest_inverter_electric_info liei ON di.deviceSn = liei.serialNumber\n" +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='deviceEntity.stationId!=null'> AND si.id=#{deviceEntity.stationId}</if>\n" +
            "<if test='deviceEntity.deviceType!=null'> AND di.deviceType=#{deviceEntity.deviceType}</if>\n" +
            "<if test='deviceEntity.merchantId!=null'> AND " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +
            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids := #{deviceEntity.merchantId}) t2) t3\n" +
            "                        where ischild != 0\n" +
            "                        UNION\n" +
            "                        SELECT id\n" +
            "                        FROM merchant_info pi\n" +
            "                        WHERE pi.id =#{deviceEntity.merchantId})</if>\n" +
            "</trim></script>")
    Integer queryCount (@Param ("deviceEntity") DeviceEntity deviceEntity);


    /**
     * 获取逆变器数量
     * @param deviceEntity
     * @return
     */
    @Select (value = "<script>SELECT di.id,di.deviceTitle,di.deviceSn,di.stationId,si.stationName,di.address,di.parentId,di.deviceType,col.deviceSn AS parentName,di.createTime \n" +
            "FROM device_info di LEFT JOIN station_info si ON di.stationId = si.id LEFT JOIN device_info col ON di.parentId = col.id\n" +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='deviceEntity.stationId!=null'> AND si.id=#{deviceEntity.stationId}</if>\n" +
            "<if test='deviceEntity.deviceType!=null'> AND di.deviceType=#{deviceEntity.deviceType}</if>\n" +
            "<if test='deviceEntity.merchantId!=null'> AND " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +
            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids := #{deviceEntity.merchantId}) t2) t3\n" +
            "                        where ischild != 0\n" +
            "                        UNION\n" +
            "                        SELECT id\n" +
            "                        FROM merchant_info pi\n" +
            "                        WHERE pi.id =#{deviceEntity.merchantId})</if>\n" +
            "LIMIT #{deviceEntity.page}, #{deviceEntity.limit}\n" +
            "</trim></script>")
    List<DeviceEntity> queryCollectionList (@Param ("deviceEntity") DeviceEntity deviceEntity);


    /**
     * 获取逆变器数量
     * @param deviceEntity
     * @return
     */
    @Select (value = "<script>SELECT COUNT(1)\n" +
            "FROM device_info di LEFT JOIN station_info si ON di.stationId = si.id LEFT JOIN device_info col ON di.parentId = col.id\n" +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='deviceEntity.stationId!=null'> AND si.id=#{deviceEntity.stationId}</if>\n" +
            "<if test='deviceEntity.deviceType!=null'> AND di.deviceType=#{deviceEntity.deviceType}</if>\n" +
            "<if test='deviceEntity.merchantId!=null'> AND " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +
            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids := #{deviceEntity.merchantId}) t2) t3\n" +
            "                        where ischild != 0\n" +
            "                        UNION\n" +
            "                        SELECT id\n" +
            "                        FROM merchant_info pi\n" +
            "                        WHERE pi.id =#{deviceEntity.merchantId})</if>\n" +
            "</trim></script>")
    Integer queryCollectionCount (@Param ("deviceEntity") DeviceEntity deviceEntity);



    /**
     * 根据设备SN查询单个设备
     * @param deviceEntity
     * @return
     */
    @Select (value = "<script>SELECT * FROM device_info di " +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='deviceEntity.deviceSn!=null'> AND di.deviceSn=#{deviceEntity.deviceSn} </if>" +
            "<if test='deviceEntity.id!=null'> AND di.id=#{deviceEntity.id} </if>" +
            "</trim></script>")
    DeviceEntity querySingle (@Param ("deviceEntity") DeviceEntity deviceEntity);


    @Update (value = "<script>UPDATE device_info di " +
            "<trim prefix=\"set\" suffixOverrides=\",\">" +
            "<if test='deviceEntity.capacity!=null'>di.capacity=#{deviceEntity.capacity},</if>" +
            "<if test='deviceEntity.deviceTitle!=null'>di.deviceTitle=#{deviceEntity.deviceTitle},</if>" +
            "<if test='deviceEntity.childSystem!=null'>di.childSystem=#{deviceEntity.childSystem},</if>" +
            "<if test='deviceEntity.matrix!=null'>di.matrix=#{deviceEntity.matrix},</if>" +
            "</trim>WHERE di.deviceSn=#{deviceEntity.deviceSn}</script>")
    Integer updateSingle (@Param ("deviceEntity") DeviceEntity deviceEntity);


    /**
     * 获取逆变器数量
     * @param deviceEntity
     * @return
     */
    @Select (value = "<script>SELECT di.id,di.deviceTitle,di.deviceSn,di.stationId,di.parentId,di.deviceType,di.power,isi.collectorSn AS parentName,isi.todayEnergy AS todayEnergy,isi.gridPower,isi.createTime \n" +
            "FROM device_info di LEFT JOIN station_info si ON di.stationId = si.id LEFT JOIN device_info col ON di.parentId = col.id\n" +
            "LEFT JOIN (SELECT cisi.*\n" +
            "FROM inverter_electric_info cisi\n" +
            "   JOIN (\n" +
            "   SELECT serialNumber, MAX(createTime) AS createTime\n" +
            "   FROM inverter_electric_info \n" +
            "   WHERE DATE_FORMAT(createTime, '%Y-%m-%d') = DATE_FORMAT(current_date(), '%Y-%m-%d')\n" +
            "   GROUP BY serialNumber\n" +
            "   ) isi_temp ON cisi.serialNumber = isi_temp.serialNumber AND cisi.createTime = isi_temp.createTime) isi\n" +
            "   ON di.deviceSn = isi.serialNumber\n" +
            "<trim prefix=\"where\" prefixOverrides=\"and | or\">" +
            "<if test='deviceEntity.stationId!=null'> AND si.id =#{deviceEntity.stationId}</if>\n" +
            "<if test='deviceEntity.deviceType!=null'> AND di.deviceType=#{deviceEntity.deviceType}</if>\n" +
            "</trim></script>")
    List<DeviceEntity> queryDeviceByStation (@Param ("deviceEntity") DeviceEntity deviceEntity);

    /**
     * 求登录用户的商户下所有电站的所有逆变器功能之和，用于显示装机容量
     * @return
     */
    @Select ( value = "SELECT *\n" +
            "FROM device_info di\n" +
            "         LEFT JOIN station_info si ON di.stationId = si.id\n" +
            "         LEFT JOIN merchant_info mi ON si.merchantId = mi.id\n" +
            "WHERE di.deviceType = 2 AND mi.id = #{stationEntity.merchantId}")
    List<DeviceEntity> queryPowerByMerchant(@Param ( "stationEntity" ) StationEntity stationEntity);

}
